﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using Garlic.DM.DataModel;

namespace DAL.SystemManagement
{
    public class VesselsDAL
    {

        #region  private fileds



        #endregion

        #region old

        /// <summary>
        /// 根据船公司或者船代理ID获取船舶信息
        /// </summary>
        /// <param name="companyID"></param>
        /// <returns></returns>
        public static DataTable GetVesselsByCompany(string companyID)
        {
            if (string.IsNullOrEmpty(companyID))
            {
                string sql = "select (VSL_ENNAME ||'('|| VSL_CD ||')' ) name,VSL_CNNAME,VSL_ID, (VSL_CNNAME||'+'||VSL_ID) IDCNName, VSL_CST_AGT_ID from st_vessels order by name";
                Database db = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = db.GetSqlStringCommand(sql);
                return db.ExecuteDataTable(dbCommand);
            }
            else
            {
                string sql = @"select (VSL_ENNAME ||'('|| VSL_CD ||')' ) name,VSL_CNNAME,VSL_ID, (VSL_CNNAME||'+'||VSL_ID) IDCNName, VSL_CST_AGT_ID from st_vessels
              where (VSL_CST_VCM_ID = :companyID or  VSL_CST_AGT_ID =:companyID) order by name";

                Database db = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = db.GetSqlStringCommand(sql);
                db.AddInParameter(dbCommand, "companyID", DbType.String, companyID);
                return db.ExecuteDataTable(dbCommand);
            }
        }



        public static DataTable GetAllVessels()
        {
            string sql = "select vsl_id,(vsl_vtpcode|| '-' ||vsl_enname) as vsname,vsl_cnname from st_vessels  order by vsl_vtpcode,vsl_enname";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            return db.ExecuteDataTable(dbCommand);

        }

        public static DataTable GetVesselsByID(int vsl_id)
        {
            string sql = "select * from st_vessels sv where sv.vsl_id=" + vsl_id;
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            return db.ExecuteDataTable(dbCommand);
        }

        //        public static DataTable GetLinersByFLT_ID(int flt_id)
        //        {
        //            string sql = @"select sl.*,sv.vsl_cnname,sv.vsl_enname,sf.flt_schdul from st_liners sl 
        //                            left join st_vessels sv on sv.vsl_id=sl.lnr_vsl_id  
        //                            left join st_flights sf on sf.flt_id=sl.lnr_flt_id
        //                            where sl.lnr_flt_id=:flt_id order by sl.lnr_vsl_cd,sl.lnr_no";
        //            Database db = DatabaseFactory.CreateDatabase();
        //            DbCommand dbCommand = db.GetSqlStringCommand(sql);
        //            db.AddInParameter(dbCommand, "flt_id", DbType.String, flt_id);
        //            return db.ExecuteDataTable(dbCommand);
        //        }

        public static DataTable GetLinersByFLT_ID(int? flt_id, int? agentID)
        {
            //            string sql = @"select sl.*,sv.vsl_cnname,sv.vsl_enname,sf.flt_schdul from st_liners sl 
            //                            left join st_vessels sv on sv.vsl_id=sl.lnr_vsl_id  
            //                            left join st_flights sf on sf.flt_id=sl.lnr_flt_id
            //                            where sl.lnr_flt_id=:flt_id ";
            if (flt_id != null)
            {
                string sql = @"select
                                sr.srt_id,sr.srt_rtchnnm,ssl.sln_name,ssl.sln_id,sf.flt_sln_id,sf.flt_id,(sv.VSL_ENNAME ||'('|| sv.VSL_CD ||')' ) name,
                                sv.vsl_cnname,sv.vsl_enname,sf.flt_schdul ,sl.* from st_liners sl 
                                left join st_vessels sv on sv.vsl_id=sl.lnr_vsl_id  
                                left join st_flights sf on sf.flt_id=sl.lnr_flt_id
                                left join st_service_lines ssl on ssl.sln_id= sf.flt_sln_id
                                left  join st_sea_routes sr on sr.srt_id = ssl.sln_srt_id 
                           where sl.lnr_flt_id=:flt_id  {0} order by sl.lnr_vsl_cd,sl.lnr_no  ";

                string whereSQL = " and ( ssl.SLN_CST_VCM_ID = {0} or  ssl.SLN_CST_AGT_ID={0}) ";
                if (agentID == null)
                    sql = string.Format(sql, "");
                else
                {
                    sql = string.Format(sql, string.Format(whereSQL, agentID));
                }

                Database db = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = db.GetSqlStringCommand(sql);
                db.AddInParameter(dbCommand, "flt_id", DbType.String, flt_id);
                return db.ExecuteDataTable(dbCommand);
            }
            else
            {
                string sql = @"select
                                sr.srt_id,sr.srt_rtchnnm,ssl.sln_name,ssl.sln_id,sf.flt_sln_id,sf.flt_id,(sv.VSL_ENNAME ||'('|| sv.VSL_CD ||')' ) name,
                                sv.vsl_cnname,sv.vsl_enname,sf.flt_schdul ,sl.* from st_liners sl 
                                left join st_vessels sv on sv.vsl_id=sl.lnr_vsl_id  
                                left join st_flights sf on sf.flt_id=sl.lnr_flt_id
                                left join st_service_lines ssl on ssl.sln_id= sf.flt_sln_id
                                left  join st_sea_routes sr on sr.srt_id = ssl.sln_srt_id  {0}
                               order by sl.lnr_vsl_cd,sl.lnr_no";

                string whereSQL = "  where ( ssl.SLN_CST_VCM_ID = {0} or  ssl.SLN_CST_AGT_ID={0}) ";
                if (agentID == null)
                    sql = string.Format(sql, "");
                else
                {
                    sql = string.Format(sql, string.Format(whereSQL, agentID));
                }
                Database db = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = db.GetSqlStringCommand(sql);
                return db.ExecuteDataTable(dbCommand);
            }
        }




        public static int InsertLiners(string lnr_vsl_cd, int lnr_flt_id, int? lnr_istep, int? lnr_estep, string lnr_ivoyage, string lnr_evoyage, string lnr_insertuser, int lnr_vsl_id, int? lnr_iamount, int? lnr_eamount)
        {
            int lnr_no = 0;
            string sql1 = "select  lnr_no from (select t.lnr_no from st_liners t where t.lnr_flt_id=:lnr_flt_id order by t.lnr_no desc) where rownum=1";
            Database db1 = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand1 = db1.GetSqlStringCommand(sql1);
            db1.AddInParameter(dbCommand1, "lnr_flt_id", DbType.String, lnr_flt_id);
            lnr_no = Convert.ToInt32(value: db1.ExecuteScalar(dbCommand1));
            lnr_no = lnr_no == 0 ? 1 : lnr_no + 1;

            string sql = @"insert into st_liners (lnr_id,lnr_vsl_cd, lnr_flt_id, lnr_no, lnr_istep, lnr_estep, lnr_ivoyage, lnr_evoyage, lnr_insertdt, lnr_insertuser, lnr_vsl_id, lnr_iamount, lnr_eamount)
                                     values ( bps_lnr_seq1.NEXTVAL,:lnr_vsl_cd, :lnr_flt_id,:lnr_no,
                                        :lnr_istep, :lnr_estep, :lnr_ivoyage, :lnr_evoyage, sysdate, :lnr_insertuser, :lnr_vsl_id, :lnr_iamount, :lnr_eamount)";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            db.AddInParameter(dbCommand, "lnr_vsl_cd", DbType.String, lnr_vsl_cd);
            db.AddInParameter(dbCommand, "lnr_flt_id", DbType.Int32, lnr_flt_id);
            db.AddInParameter(dbCommand, "lnr_no", DbType.Int32, lnr_no);

            if (lnr_istep == null)
                db.AddInParameter(dbCommand, "lnr_istep", DbType.Int32, DBNull.Value);
            else
                db.AddInParameter(dbCommand, "lnr_istep", DbType.Int32, lnr_istep);

            if (lnr_estep == null)
                db.AddInParameter(dbCommand, "lnr_estep", DbType.Int32, DBNull.Value);
            else
                db.AddInParameter(dbCommand, "lnr_estep", DbType.Int32, lnr_estep);

            db.AddInParameter(dbCommand, "lnr_ivoyage", DbType.String, lnr_ivoyage);
            db.AddInParameter(dbCommand, "lnr_evoyage", DbType.String, lnr_evoyage);
            db.AddInParameter(dbCommand, "lnr_insertuser", DbType.String, lnr_insertuser);
            db.AddInParameter(dbCommand, "lnr_vsl_id", DbType.Int32, lnr_vsl_id);

            if (lnr_iamount == null)
                db.AddInParameter(dbCommand, "lnr_iamount", DbType.Int32, DBNull.Value);
            else
                db.AddInParameter(dbCommand, "lnr_iamount", DbType.Int32, lnr_iamount);

            if (lnr_eamount == null)
                db.AddInParameter(dbCommand, "lnr_eamount", DbType.Int32, DBNull.Value);
            else
                db.AddInParameter(dbCommand, "lnr_eamount", DbType.Int32, lnr_eamount);



            return db.ExecuteNonQuery(dbCommand);

        }

        private object GetData(object value)
        {
            if (value == null || string.IsNullOrEmpty(value.ToString()))
                return DBNull.Value;
            return value;
        }

        /// <summary>
        /// 执行添加、修改、删除操作
        /// </summary>
        /// <param name="operationType"></param>
        /// <param name="userID"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public string InsertUpdateDeleteOperation(int operationType, int userID, WEB_VM_LINERS model)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("Web_SP_Process_LinerInfo");
             db.AddInParameter(dbCommand, "P_OperationType", DbType.Decimal, operationType);

             db.AddInParameter(dbCommand, "P_User_ID", DbType.Decimal, userID );

             db.AddInParameter(dbCommand, "P_LNR_ID", DbType.Decimal,  GetData(model.LNR_ID));

             db.AddInParameter(dbCommand, "P_LNR_VSL_CD", DbType.String, GetData(model.LNR_VSL_CD) );
              db.AddInParameter(dbCommand, "P_LNR_VSL_ID", DbType.Decimal, GetData(model.LNR_VSL_ID));

             db.AddInParameter(dbCommand, "P_LNR_ISTEP", DbType.Decimal, GetData(model.LNR_ISTEP));

             db.AddInParameter(dbCommand, "P_LNR_ESTEP", DbType.Decimal, GetData(model.LNR_ESTEP));

             db.AddInParameter(dbCommand, "P_LNR_IVOYAGE", DbType.String,GetData(model.LNR_IVOYAGE) );


             db.AddInParameter(dbCommand, "P_LNR_EVOYAGE", DbType.String,GetData(model.LNR_EVOYAGE) );

             db.AddInParameter(dbCommand, "P_LNR_SERLINE_ID", DbType.Decimal, GetData(model.LNR_SERLINE_ID));

             db.AddInParameter(dbCommand, "P_LNR_IAMOUNT", DbType.Decimal, GetData(model.LNR_IAMOUNT));
               db.AddInParameter(dbCommand, "P_LNR_EAMOUNT", DbType.Decimal, GetData(model.LNR_EAMOUNT));
               db.AddInParameter(dbCommand, "P_LNR_FLT_ID", DbType.Decimal,GetData(model.LNR_FLT_ID) );
               db.AddOutParameter(dbCommand, "ReturnValue",DbType.String, 100);

             db.ExecuteNonQuery(dbCommand);

            string str =   db.GetParameterValue(dbCommand, "ReturnValue").ToString();
           return str;

        }

        public static int Delete_st_liners(int lnr_id)
        {
            string sql = "delete from st_liners sl where sl.lnr_id=:lnr_id";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            db.AddInParameter(dbCommand, "lnr_id", DbType.Int32, lnr_id);
            return db.ExecuteNonQuery(dbCommand);
        }

        public static int Update_st_liners(int lnr_id, string lnr_ivoyage, string lnr_evoyage, string lnr_istep, string lnr_iamount, string lnr_eamount, string lnr_estep, string lnr_lstupuser)
        {
            //            string sql =string.Format(@"update st_liners sl set lnr_ivoyage='{0}', 
            //                                lnr_evoyage='{1}',
            //                                lnr_istep='{2}',
            //                                lnr_iamount='{3}',
            //                                lnr_eamount='{4}',
            //                                lnr_estep='{5}',
            //                                lnr_lstupuser='{6}'
            //                                where lnr_id={7}",lnr_ivoyage, lnr_evoyage,lnr_istep,lnr_iamount,lnr_eamount, lnr_estep,lnr_lstupuser,lnr_id);
            //            Database db = DatabaseFactory.CreateDatabase();
            //            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            string sql = @"update st_liners sl set lnr_ivoyage=:lnr_ivoyage, 
                                lnr_evoyage=:lnr_evoyage,
                                lnr_istep=:lnr_istep,
                                lnr_iamount=:lnr_iamount,
                                lnr_eamount=:lnr_eamount,
                                lnr_estep=:lnr_estep,
                                lnr_lstupuser=:lnr_lstupuser
                                where lnr_id=:lnr_id";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            if (String.IsNullOrEmpty(lnr_istep))
                db.AddInParameter(dbCommand, "lnr_istep", DbType.Int32, DBNull.Value);
            else
                db.AddInParameter(dbCommand, "lnr_istep", DbType.Int32, lnr_istep);

            if (String.IsNullOrEmpty(lnr_estep))
                db.AddInParameter(dbCommand, "lnr_estep", DbType.Int32, DBNull.Value);
            else
                db.AddInParameter(dbCommand, "lnr_estep", DbType.Int32, lnr_estep);

            db.AddInParameter(dbCommand, "lnr_ivoyage", DbType.String, lnr_ivoyage);
            db.AddInParameter(dbCommand, "lnr_evoyage", DbType.String, lnr_evoyage);

            if (String.IsNullOrEmpty(lnr_iamount))
                db.AddInParameter(dbCommand, "lnr_iamount", DbType.Int32, DBNull.Value);
            else
                db.AddInParameter(dbCommand, "lnr_iamount", DbType.Int32, lnr_iamount);

            if (String.IsNullOrEmpty(lnr_eamount))
                db.AddInParameter(dbCommand, "lnr_eamount", DbType.Int32, DBNull.Value);
            else
                db.AddInParameter(dbCommand, "lnr_eamount", DbType.Int32, lnr_eamount);

            db.AddInParameter(dbCommand, "lnr_lstupuser", DbType.String, lnr_lstupuser);
            db.AddInParameter(dbCommand, "lnr_id", DbType.Int32, lnr_id);
            return db.ExecuteNonQuery(dbCommand);
        }

        #endregion


        #region  new


        /// <summary>
        /// 获取web_vm_liners的所有信息
        /// </summary>
        /// <param name="strWhere"></param>
        /// <param name="strOrder"></param>
        /// <returns></returns>
        public DataTable GetTable(string strWhere, string strOrder)
        {
            string sql = "select * from web_vm_liners";
            //  string query = string.Empty;
            if (!string.IsNullOrEmpty(strWhere))
                sql += " where " + strWhere;
            if (!string.IsNullOrEmpty(strOrder))
                sql += " order by " + strOrder;

            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            DataTable dt = db.ExecuteDataTable(dbCommand);
            if (dt != null)
                dt.TableName = "web_vm_liners";
            return dt;

        }

        #endregion






    }
}
